package ups.Database;

import java.text.ParseException;
import java.util.Date;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Hashtable;
import java.util.LinkedHashSet;
import java.util.Set;

import ups.Common.Port;
import ups.Schedule.OrderedScheduleItemComparator;
import ups.Schedule.ScheduleItem;
import ups.Schedule.ScheduleItemComparator;
import ups.Schedule.ScheduleTime;
import ups.Schedule.DaysOfWeek;

public class ScheduleDatabase extends Database {
	
	private ShipDatabase shipDB = null;
	private ArrayList<String> shipNames = null;
	
	private PortDatabase portDB = null;
	private ArrayList<Port> ports = null;
	
	/* Database Column Keys */
	private String SCHEDULE_TABLE 	= "schedule";
	private String SHIP_ID_KEY		= "ship_id";
	private String PORT_ID_KEY		= "port_id";
	private String TIME_OF_DAY_KEY	= "time";
	private String DAY_OF_WEEK_KEY	= "dayofweek";
	private String START_DATE_KEY	= "startdate";	
	private String END_DATE_KEY		= "enddate";
	
	public ScheduleDatabase( String hostName, int portNum, String databaseName, String username, String password ) {
		super( hostName, portNum, databaseName, username, password );
		shipDB = new ShipDatabase( hostName, portNum, databaseName, username, password );
		portDB = new PortDatabase(hostName , portNum, databaseName, username, password);
		ports = portDB.getPorts();
	}
	
	
	public int getRoute( Date firstTime, String port, String dest ) {
		int portID = getPortID( port );
		int destID = getPortID( dest );
		String weekDayQuery = getActiveDaysQuery( firstTime );
		
		
		String query = "SELECT * FROM route" +
					   " WHERE route.start_port = " + portID +
					   " AND route.end_port = " + destID +
					   " AND (" + weekDayQuery + ")" +
					   " AND \"" + getDateFormat().format(firstTime) + "\" BETWEEN route.start_date AND route.end_date";
		int nearestRoute = 0;
		try {
			ResultSet rs = super.execute( query );
			int nearestRouteTime = Integer.MAX_VALUE;
			
			Calendar startTime = Calendar.getInstance();
			startTime.setTime( firstTime );
			
			while( rs.next() ) {
				Calendar scheduleStartTime = Calendar.getInstance();
				scheduleStartTime.setTime( getTimeFormat().parse( rs.getString("start_time")));
				
				int startTimeAsMinutes = 60 * startTime.get( Calendar.HOUR ) + startTime.get( Calendar.MINUTE );
				int scheduledAsMinutes = 60 * scheduleStartTime.get( Calendar.HOUR ) + scheduleStartTime.get( Calendar.MINUTE );
				int difference = Math.abs( startTimeAsMinutes - scheduledAsMinutes );
			//	int difference = (startTime.get( Calendar.HOUR ) - scheduleStartTime.get( Calendar.HOUR ) - 1) * 60 + startTime.get( Calendar.MINUTE ) - scheduleStartTime.get( Calendar.MINUTE );
				//int difference = startTime.get( Calendar.HOUR ) * 60 + startTime.get( Calendar.MINUTE ) -
					//			 scheduleStartTime.get( Calendar.HOUR ) * 60 + scheduleStartTime.get( Calendar.MINUTE );
				if( difference < nearestRouteTime ) {
					System.out.println( "Changing routeID to " + rs.getInt( "route_id") + " because the time diff is " + difference + " (compare : " + firstTime.toString() + " to " + rs.getString( "route.start_time"));
					nearestRouteTime = difference;
					nearestRoute = rs.getInt( "route_id");
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch ( ParseException pex ) {
			pex.printStackTrace();
		}
		
		return nearestRoute;
	}
	
	public int getPortID( String portName ) {
		String query = "SELECT * FROM ports WHERE ports.name = \"" + portName + "\";";
		try {
			ResultSet rs = super.execute( query );
			rs.next();
			return rs.getInt( "port_id" );
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return -1;
	}
	
	public String getActiveDaysQuery( Date day ) {
		String activeDay = null;
		Calendar cal = Calendar.getInstance();
		cal.setTime( day );
		int week_day = cal.get( Calendar.DAY_OF_WEEK );
		switch( week_day ) {
			case Calendar.MONDAY:
			case Calendar.TUESDAY:
			case Calendar.WEDNESDAY:
			case Calendar.THURSDAY:
			case Calendar.FRIDAY:
				activeDay = "route.day_of_week = \"Monday_Through_Friday\" OR " +
							"route.day_of_week = \"Monday_Through_Friday_And_Holidays\" OR " +
							"route.day_of_week = \"Daily\"";
				break;
			case Calendar.SATURDAY:
			case Calendar.SUNDAY:
				activeDay = "route.day_of_week = \"Saturday_And_Sunday\" OR " +
							"route.day_of_week = \"Saturday_Sunday_and_Holidays\" OR " +
							"route.day_of_week = \"Daily\"";
				break;
			default:
				System.err.println("Not a day of week?");
		}
		return activeDay;
	}
	
	public ArrayList<ScheduleItem> getSchedule( int route ) {
		String query = "SELECT * from ports, schedule" +
					   " WHERE ports.port_id = schedule.port_id" + 
					   " AND schedule.route_id = " + route +
					   " ORDER BY schedule.day_index";
		
		ArrayList<ScheduleItem> items = new ArrayList<ScheduleItem>();
		
		ResultSet rs;
		try {
			rs = execute( query );
			while( rs.next() ) {
				Date date = getTimeFormat().parse( rs.getString( "time" ) );
				ScheduleTime time = new ScheduleTime( date.getHours(), date.getMinutes() );
				ScheduleItem item = new ScheduleItem( time, rs.getString("ports.name"), DaysOfWeek.Daily);
				items.add( item );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return items;
	}
	
	
	public void putItems( ArrayList<ScheduleItem> items ) {
		
		for( ScheduleItem item : items ) {
			System.out.println( item.toString() );
		}
		
		/* Sort Schedules By Ship, Then By Time */
		Hashtable<String, Hashtable<String, ArrayList<ScheduleItem>>> schedules = new Hashtable<String, Hashtable<String, ArrayList<ScheduleItem>>>();
		
		// This removes all duplicates. Not sure if it preserves the ordering ( we could sort on that again if we have to )
		System.out.println( items.size() + " items, with dupes");
		
		LinkedHashSet<ScheduleItem> set = new LinkedHashSet<ScheduleItem>();
		
		int index = items.size();
		while( index-- != 0) {
			if(! set.add( items.get( index )) ) {
				System.out.println("Didn't add something because it was a dupe");
			}
		}
		
		items = new ArrayList<ScheduleItem>( set );
		
		//items =  new ArrayList<ScheduleItem>(new LinkedHashSet<ScheduleItem>(( items )));
		System.out.println( items.size() + " items, no dupes");
		
		for( ScheduleItem item : items ) {
			String name = item.getShipName() + item.getOperationDays().toString();
			if( schedules.get(name ) == null ) {
				schedules.put( name, new Hashtable<String, ArrayList<ScheduleItem>>());
			}
			String port = item.getLeavingFrom();
			if( ! schedules.get( name ).containsKey( port )) {
				schedules.get( name ).put( port, new ArrayList<ScheduleItem>());
			}
			
			schedules.get( name ).get( port ).add( item );
		}
		
		Hashtable<String, ArrayList<ScheduleItem>> builtScheduleByName = new Hashtable<String, ArrayList<ScheduleItem>>();
		for( String shipNameKey : schedules.keySet() ) {
			Hashtable<String, ArrayList<ScheduleItem>> itemsForName = schedules.get( shipNameKey );
			// Sort these by internal ordering?
			for( String portKey : itemsForName.keySet() ) {
				Collections.sort( itemsForName.get( portKey ), new OrderedScheduleItemComparator() );
			}
		
			if( shipNameKey.equals("KlahowyaMonday_Through_Friday")) {
				System.out.println( shipNameKey );
				for( String key : itemsForName.keySet()) {
					for( ScheduleItem anItem : itemsForName.get( key)) {
						System.out.println( anItem.toString() );
					}
				}
			}
			
			System.out.println(shipNameKey);
			ArrayList<ScheduleItem> builtSchedule = new ArrayList<ScheduleItem>();
			while( !empty( itemsForName )) {
				builtSchedule.add( nextItem( itemsForName ));
			}
			System.out.println("\n\n---------------------------");
			builtScheduleByName.put( shipNameKey, builtSchedule );
		}
		
		/*
		for( String key : schedules.keySet() ) {
			Collections.sort( schedules.get( key ), new OrderedScheduleItemComparator() );
		}
		*/
		
		for( String key : builtScheduleByName.keySet() ) {
			System.out.println(key);
			ArrayList<ScheduleItem> shipSchedule = builtScheduleByName.get( key );
			
			/* Add a new route entry for this one */
			ScheduleItem firstItem = shipSchedule.get( 0 );
			ScheduleItem secondItem = shipSchedule.get( 1 );
			
			if( firstItem.getLeavingFrom().equals( secondItem.getLeavingFrom() )) {
				for( ScheduleItem item : shipSchedule ) {
					System.out.println( item );
				}
			}
			
			Hashtable<String,String> newRoute = new Hashtable<String,String>();
			newRoute.put( "start_port", new Integer( indexOfPortNamed( firstItem.getLeavingFrom())).toString() );
			newRoute.put( "end_port",  new Integer( indexOfPortNamed( secondItem.getLeavingFrom())).toString());
			newRoute.put( "start_time", firstItem.getTime().toString() );
			newRoute.put( "day_of_week", firstItem.getOperationDays().toString() );
			newRoute.put( "start_date", "2005-01-01" );
			newRoute.put( "end_date", "2008-01-01" );
			
			int route = this.put( "route", newRoute );
			
			
			/* Add each item in the schedule */
			int counter = 0;
			for( ScheduleItem item : shipSchedule ) {
				Hashtable<String,String> newItem = new Hashtable<String,String>();
				newItem.put( "route_id" , new Integer( route ).toString() );
				if( indexOfPortNamed( item.getLeavingFrom() ) == -1 ) {
					System.out.println( item.getLeavingFrom() );
				}
				newItem.put( "port_id" , new Integer( indexOfPortNamed( item.getLeavingFrom() ) ).toString() );
				newItem.put( "time", item.getTime().toString() );
				newItem.put( "day_index", new Integer( counter ).toString() );
				counter++;
				
				this.put( "schedule", newItem );
			}
		}
		
		
	}
	

	public int indexOfPortNamed( String name ) {
		int index;
		for( index = 0; index < ports.size(); index++ ) {
			if( ports.get( index ).getName().equals( name )) {
				return index+1;
			}
		}	
		return -1;
	}
	
	public boolean empty( Hashtable<String, ArrayList<ScheduleItem>> items ) {
		for( String key : items.keySet() ) {
			if( items.get( key ).size() > 0 ) {
				return false;
			}
		}
		return true;
	}
	
	public ScheduleItem nextItem( Hashtable<String, ArrayList<ScheduleItem>> items ) {
		
		Set<String> keys = items.keySet();
		
		ScheduleItem earliestItem = null;
		String		 earliestList = null;
		
		for( String key : keys ) {
			ArrayList<ScheduleItem> list = items.get( key );
			if( !list.isEmpty() ) {
				ScheduleItem top = list.get( 0 );
				System.out.print( key + ":" + top.getTime().toString() + " | " );
				if( earliestItem == null ||
						top.isBefore( earliestItem )) {
					earliestItem = top;
					earliestList = key;
				}
			}
		}
		System.out.println(" Earliest is " + earliestItem.getLeavingFrom() + "(" + earliestItem.getTime().toString() + ")");
		
		items.get( earliestList ).remove( earliestItem );
		return earliestItem;
	}
	
	/*public void append( ArrayList<ScheduleItem> items ) {
		if( shipNames == null ) {
			shipNames = shipDB.getShipNames();
		}
		
		if( ports == null ) {
			ports = portDB.getPorts();
		}
		
		for( ScheduleItem scheduleItem : items ) {
			Hashtable<String,String> row = new Hashtable<String,String>();
			
			row.put( SHIP_ID_KEY, String.valueOf( shipNames.indexOf( scheduleItem.getShipName())));
			row.put( PORT_ID_KEY, String.valueOf( this.indexOfPortNamed( scheduleItem.getLeavingFrom())));
			row.put( TIME_OF_DAY_KEY, super.getDateFormat().format( scheduleItem.getTime() ) );
			row.put( DAY_OF_WEEK_KEY, "1");
			row.put( START_DATE_KEY, super.getDateFormat().format( new Date() ));
			row.put( END_DATE_KEY, super.getDateFormat().format( new Date() ));
			super.put( SCHEDULE_TABLE , row );
		}
	}
	
	/*
	public int indexOfPortNamed( String name ) {
		int index;
		for( index = 0; index < ports.size(); index++ ) {
			if( ports.get( index ).getName().equals( name )) {
				return index;
			}
		}
		return -1;
	}
	
	public void delete() throws SQLException {
		super.delete( SCHEDULE_TABLE );
	}
/*
	public ArrayList<ScheduleItem> getSchedule( String shipName, Date day ) {
		ArrayList<ScheduleItem> items = new ArrayList<ScheduleItem>();
		try {
			// Figure out which Day Of Week
			Calendar cal = Calendar.getInstance();
			System.out.println( day.toString() );
			cal.setTime( day );
			int dayOfWeek = cal.get( Calendar.DAY_OF_WEEK );
			String dayOfWeekString = null;
			System.out.println("Day Of Week is " + dayOfWeek );
			switch( dayOfWeek ) {
				case Calendar.MONDAY:
				case Calendar.TUESDAY:
				case Calendar.WEDNESDAY:
				case Calendar.THURSDAY:
				case Calendar.FRIDAY:
					dayOfWeekString = "Monday_Through_Friday";
					break;
				case Calendar.SATURDAY:
				case Calendar.SUNDAY:
					dayOfWeekString = "Saturday_And_Sunday";
					break;
				default:
					System.err.println("Not a day of week?");
			}
			System.out.println( dayOfWeekString );
			
			Statement stmt = this.getConnection().createStatement();
			
			// Build Query
			String query = "SELECT * FROM ports, ships, schedule " +
							"WHERE ports.port_id = schedule.port_id AND " +
							"ships.ship_id = schedule.ship_id AND " +
							"ships.name = \"" + shipName + "\" AND "  +
							"schedule.dayofweek = \"" + dayOfWeekString + "\" AND " +
							"\"" + getDateFormat().format( day ) + "\" BETWEEN schedule.startdate AND schedule.enddate " +
							"ORDER BY schedule.day_index";
			System.out.println( query );
			ResultSet rs = stmt.executeQuery( query );
			
			while( rs.next() ) {
			//	public ScheduleItem( ScheduleTime time, String shipName, String leavingFrom, DaysOfWeek operationDays ) {
				Date date = getTimeFormat().parse( rs.getString( "time" ) );
				ScheduleTime time = new ScheduleTime( date.getHours(), date.getMinutes() );
				DaysOfWeek monday_through_friday = DaysOfWeek.Monday_Through_Friday;
				ScheduleItem item = new ScheduleItem( time, rs.getString( "ships.name"), rs.getString( "ports.name" ), monday_through_friday );
			//	System.out.println( item.toString() );
				items.add( item );
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch( ParseException pex ) {
			pex.printStackTrace();
		}
		
		
		return items;
	}
	*/
}
